package com.gitee.loyo.jdbc

import com.fasterxml.jackson.annotation.JsonAutoDetect
import com.fasterxml.jackson.annotation.JsonInclude
import com.fasterxml.jackson.databind.ObjectMapper
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateDeserializer
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer
import com.fasterxml.jackson.datatype.jsr310.deser.LocalTimeDeserializer
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateSerializer
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer
import com.fasterxml.jackson.datatype.jsr310.ser.LocalTimeSerializer
import com.fasterxml.jackson.module.kotlin.jacksonObjectMapper
import com.gitee.loyo.H2TestCase
import com.gitee.loyo.Helper
import com.gitee.loyo.JacksonMapper
import com.gitee.loyo.KSQL
import com.gitee.loyo.act.remote.*
import com.gitee.loyo.interfaces.TypeReference
import org.junit.After
import org.junit.Assert.*
import org.junit.Before
import org.junit.Test
import java.math.BigDecimal
import java.time.LocalDate
import java.time.LocalDateTime
import java.time.LocalTime
import java.time.format.DateTimeFormatter

class JDBCActiveRemoteTestCase : H2TestCase(){

    init {
        val objectMapper: ObjectMapper = jacksonObjectMapper()
        objectMapper.setVisibility(objectMapper.serializationConfig.defaultVisibilityChecker
            .withFieldVisibility(JsonAutoDetect.Visibility.ANY)
            .withGetterVisibility(JsonAutoDetect.Visibility.NONE)
            .withSetterVisibility(JsonAutoDetect.Visibility.NONE)
            .withCreatorVisibility(JsonAutoDetect.Visibility.NONE))
        objectMapper.setSerializationInclusion(JsonInclude.Include.NON_NULL)
        val javaTimeModule = JavaTimeModule()
        javaTimeModule.addSerializer(LocalDateTime::class.java, LocalDateTimeSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")))
        javaTimeModule.addDeserializer(LocalDateTime::class.java, LocalDateTimeDeserializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")))
        javaTimeModule.addSerializer(LocalDate::class.java, LocalDateSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
        javaTimeModule.addDeserializer(LocalDate::class.java, LocalDateDeserializer(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
        javaTimeModule.addSerializer(LocalTime::class.java, LocalTimeSerializer(DateTimeFormatter.ofPattern("HH:mm:ss")))
        javaTimeModule.addDeserializer(LocalTime::class.java, LocalTimeDeserializer(DateTimeFormatter.ofPattern("HH:mm:ss")))
        objectMapper.registerModule(javaTimeModule)

        DualModels.getClassName = {
            "com.gitee.loyo.act.${it.substring(it.lastIndexOf('.') + 1)}"
        }
        DualModels.execute = {
            val str = objectMapper.writeValueAsString(it)
            println("DualModels.request: $str")
            objectMapper.readValue(str, ModelExecution::class.java).execute().also {
                println("DualModels.response: $it")
            }
        }
        DualModels.invoke = {
            val str = objectMapper.writeValueAsString(it)
            println("DualModels.invoke: $str")
            objectMapper.readValue(str, RemoteInvocation::class.java).invoke()?.let {
                objectMapper.writeValueAsString(it)
            }?.also {
                println("DualModels.response: $it")
            }?:""
        }
        DualModels.mapper = JacksonMapper(objectMapper)
    }

    @Before
    fun init(){
        KSQL.tryGetConnection = {
            Conn.current()?:
            Conn.create(JDBC.ds()?.connection?:throw NullPointerException("未发现DataSource")).also {
                it.executeOnce = true
            }
        }
        dataSource.connection.use { conn ->
            conn.createStatement().use { stmt ->
                stmt.addBatch("""
                    insert into t_user(user_id, user_name, sex, active)values(1, 'admin1', 1, 0)
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_user(user_id, user_name, sex, active)values(2, 'admin2', 2, 0)
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_job(employee_id, user_id, title, salary)values(1, 1, 'staff1', 1234.50)
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_job(employee_id, user_id, title, salary)values(2, 1, 'staff2', 5678.49)
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_user_profile(user_id, education)values(1, '大专')
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_user_profile(user_id, education)values(2, '本科')
                """.trimIndent())

                stmt.executeBatch()
            }
            conn.commit()
        }
        JDBC.init(dataSource)
    }

    @After
    fun close(){
        dataSource.connection.use {
            it.createStatement().use { stmt ->
                stmt.execute("truncate table t_user")
                stmt.execute("truncate table t_job")
                stmt.execute("truncate table t_user_profile")
                stmt.execute("truncate table t_daily_log_20210806")
            }
            it.commit()
        }
    }

    @Test
    fun testQueryOne(){
        val user = TUserModel.findByID(1L)
        assertNotNull(user)
        assertEquals("admin1", user?.userName)
        assertEquals(1, user?.sex)

        val job = TJobModel.findByIDs{
            arrayOf(employee_id to 1, user_id to 1)
        }
        assertNotNull(job)
        assertEquals("staff1", job?.title)
    }

    @Test
    fun testReload(){
        val user = TUserModel.findByID(1L)
        assertEquals(User(userId = 1L).apply { reload() }, user)
    }

    @Test
    fun testQueryList(){
        val userList1 = TUserModel.find{
            user_name eq "admin1"
            (sex eq 1) or (sex eq 2)
            user_id `in` arrayOf(1, 2)
        }
        assertEquals(1, userList1.size)

        val userList2 = TUserModel.find {
            select(user_id, user_name)
            where {
                (user_name like "admin%") and
                    ((sex eq 1) or (sex eq 2)) and
                    (user_id `in` arrayOf(1, 2))
            }
            order { asc, desc ->
                asc(user_id)
                desc(user_name)
            }
            limit(2)
        }
        assertEquals(2, userList2.size)

        val userList3 = TUserModel.find{
            group(user_id, user_name)
        }
        assertEquals(2, userList3.size)

        val userList4 = TUserModel.find(User(userId = 1))
        assertEquals(1, userList4.size)
    }

    @Test
    fun testQueryOrderedList(){
        val userList1 = TUserModel.take(3)
        assertEquals(2, userList1.size)
        assertEquals(1L, userList1[0].userId)
        assertEquals(2L, userList1[1].userId)

        val userList2 = TUserModel.last(3)
        assertEquals(2, userList2.size)
        assertEquals(2L, userList2[0].userId)
        assertEquals(1L, userList2[1].userId)
    }

    @Test
    fun testQueryPage(){
        val (count, list) = TUserModel.findPage{ limit(0, 5) }
        assertEquals(2, count)
        assertEquals(2, list.size)
    }

    @Test
    fun testNullable(){
        val list1 = TUserModel.find{
            ifNotNull(user_id eq 1L)
        }
        assertEquals(1, list1.size)
        val list2 = TUserModel.find{
            ifNotNull(user_id eq null)
        }
        assertEquals(2, list2.size)
        val list3 = TUserModel.find{
            ifNotNull(user_id eq 1)
            ifNotNull((user_name like null) or (sex eq 2))
        }
        assertEquals(1, list3.size)
        val list4 = TUserModel.find{
            ifNotNull(user_id eq 1)
            ifNotNull((user_id `in` null) or (sex eq 2))
        }
        assertEquals(1, list4.size)
    }

    @Test
    fun testCount(){
        val number1 = TUserModel.count{
            user_name eq "admin1"
            (sex eq 1) or (sex eq 2)
            user_id `in` arrayOf(1, 2)
        }
        assertEquals(1, number1)

        val number2 = TUserModel.findResult{
            select(count(user_id) `as` "number")
            where {
                user_name eq "admin1"
                (sex eq 1) or (sex eq 2)
                user_id `in` arrayOf(1, 2)
            }
        }.`as`(Int::class)
        assertEquals(1, number2)
    }

    @Test
    fun testSum(){
        val userIdSum = TUserModel.findOther<Long>{
            select(sum(user_id))
        }
        assertEquals(3L, userIdSum)
    }

    @Test
    fun testMaxMin(){
        val maxUserId = TUserModel.findOther<Long>{
            select(max(user_id))
        }
        assertEquals(2L, maxUserId)

        val minUserId = TUserModel.findOther<Long>{
            select(min(user_id))
        }
        assertEquals(1L, minUserId)
    }

    @Test
    fun testAvg(){
        val avgUserId = TUserModel.findOther<Float>{
            select(avg(user_id))
        }
        assertEquals(2.0f, avgUserId)
    }

    @Test
    fun testAggregateFunction(){
        val groupList1 = TUserModel.findOthers<Pair<Long, Int>>{
            select(user_id `as` "first", count(user_id) `as` "second")
            group(user_id)
        }
        groupList1.forEach {
            assertEquals(1, it.second)
        }
        val groupList2 = TUserModel.findOthers<Pair<Long, Int>>{
            select(user_id, count(user_id))
            inner join TUserProfileModel on user_id
            group(user_id)
        }
        groupList2.forEach {
            assertEquals(1, it.second)
        }

        TUserModel.add(User(userId = 3L, userName = "user0", sex = 1, false))
        val groupList3 = TUserModel.findOthers<Pair<Int, Int>>{
            select(sex, count(user_id) `as` "number")
            group(sex)
            having { count(user_id) gt 1 }
        }
        groupList3.forEach {
            assertEquals(2, it.second)
        }
    }

    @Test
    fun testSQLFunction(){
        assertEquals(2, TUserModel.count{ instr(user_name, "ad") eq 1 })
        assertEquals(1, TUserModel.count{ substr(user_name, 6) eq "1" })
        assertEquals(2, TUserModel.count{ substr(user_name, 1, 5) eq "admin" })
        assertEquals(BigDecimal(1235), TJobModel.find{ select(round(salary) `as` salary) }[0].salary)
        assertEquals(BigDecimal(1235), TJobModel.find{ select(ceil(salary) `as` salary) }[0].salary)
        assertEquals(BigDecimal(1234), TJobModel.find{ select(floor(salary) `as` salary) }[0].salary)
        assertEquals(BigDecimal(10), TJobModel.find{ select(mod(floor(salary), 12) `as` salary) }[0].salary)
    }

    @Test
    fun testCreate(){
        val user = User(userName = "guest", sex = 1)
        val userId = TUserModel.create(user)
        assertNotNull(userId)
    }

    @Test
    fun testSave(){
        TUserModel.findByID(1L)?.apply {
            userName = "newAdmin"
            save()
        }
        val user = TUserModel.findByID(1L)
        assertNotNull(user)
        assertEquals("newAdmin", user?.userName)
        assertEquals(1, user?.sex)
    }

    @Test
    fun testSaveAttr(){
        TUserModel.findByID(1L)?.apply {
            userName = "newAdmin"
            sex = 3
            saveAttr(this::userName, this::sex)
        }
        val user = TUserModel.findByID(1L)
        assertEquals("newAdmin", user?.userName)
        assertEquals(3, user?.sex)

        TJobModel.findByIDs {
            arrayOf(employee_id to 1L, user_id to 1L)
        }?.apply {
            title = "ceo"
            saveAttr(this::title)
        }
        val job = TJobModel.findByIDs {
            arrayOf(employee_id to 1L, user_id to 1L)
        }
        assertEquals("ceo", job?.title)
    }

    @Test
    fun testRemove(){
        User(userId = 1L).remove()
        assertEquals(1, TUserModel.count())

        Job(employeeId = 1, userId = 1).remove()
        assertEquals(1, TJobModel.count())

        TUserModel.remove{
            user_name eq "qq"
        }
        assertEquals(1, TUserModel.count())
    }

    @Test
    fun testFunction(){
        val users = User(userName = "admin1").findAllFromSameUserName()
        assertEquals("admin1", users.firstOrNull()?.userName)
        assertEquals(1, users.firstOrNull()?.sex)
    }

    @Test
    fun testJoin(){
        val userList = TUserModel.find{
            select(user_id)
            inner join TJobModel on user_id and { title like "staff%" }
            where{
                sex eq 1
            }
        }

        assertEquals(2, userList.size)
        assertEquals(1L, userList.first().userId)
    }

    @Test
    fun testRef(){
        val profile = TUserModel.findByID(1)?.also {
            println("Its lazy")
        }?.profile

        assertNotNull(profile)
        assertEquals("大专", profile?.education)

        val jobs = TUserModel.findByID(1)?.also {
            println("Its lazy")
        }?.jobs

        assertEquals(2, jobs?.size)
    }

    @Test
    fun testCompanionDao(){
        val rows = TUserModel.removeAllAdmin()

        assertEquals(2, rows)
    }

    @Test
    fun testPattern(){
        TDailyLogModel["20210806"].apply {
            add(DailyLog(logTime = LocalDateTime.now()))
            assertEquals(1, count())
        }
    }
}